﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using Automobile.service.Common;
using System.Data.SqlClient;
using System.Data;
using System.Web;
using System.Web.Script.Serialization;
namespace Automobile.service.DAL
{
  public  class PurchaseDAL
    {
      SQLHelper helper = new SQLHelper();
      /// <summary>
      /// 添加采购进货订单
      /// </summary>
      /// <returns></returns>
      public int AddPurchase(Purchases pu,string xml)
      {
          SqlParameter[] par = {
                            new SqlParameter("@Purchase_Type_State",pu.Purchase_Type_State), 
                            new SqlParameter("@Purchase_State",pu.Purchase_State), 
                            new SqlParameter("@Supplier_Id",pu.Supplier_Id), 
                            new SqlParameter("@WareHose_Id",pu.WareHose_Id), 
                            new SqlParameter("@Employee_Id",pu.Employee_Id),
                            new SqlParameter("@SumMoney",pu.SumMoney),
                            new SqlParameter("@xml",xml)
                             };
          return helper.ExecNonquery_Proc("Proc_AddPurchse",par);
      }

      /// <summary>
      /// 查询订单信息(根据日期区间)
      /// </summary>
      /// <returns></returns>
      public List<Purchases> SearchPurchaseByDate(int index, int size, string firstdate, string lastdate, out int count)
      {
          
          SqlParameter[] paras =new SqlParameter[] {
                            new SqlParameter("@firstdate",firstdate), 
                            new SqlParameter("@lastdate",lastdate), 
                            new SqlParameter("@index",index), 
                            new SqlParameter("@size",size), 
                            new SqlParameter("@count",SqlDbType.Int,4)
                             };
          paras[0].Value=firstdate;
          paras[1].Value = lastdate;
          paras[2].Value = index;
          paras[3].Value = size;
          paras[4].Direction = ParameterDirection.Output; //输出参数
          DataTable table = helper.ExecDataTable_Proc("Proc_ShowPurchase", paras);
          count = int.Parse(paras[4].Value.ToString());
          List<Purchases> list = new List<Purchases>();
          foreach (DataRow i in table.Rows)
          {
              Purchases p = new Purchases();
              p.Purchase_Id = Convert.ToInt32(i["Purchase_Id"]);
              p.Purchase_date = Convert.ToDateTime(i["Purchase_date"]);
              p.Purchase_State = Convert.ToInt32(i["Purchase_State"]);
              p.Supplier_Name = i["Supplier_Name"].ToString();
              p.WareHose_Name = i["WareHose_Name"].ToString();
              p.Employee_Name = i["Employee_Name"].ToString();
              list.Add(p);
          }
          return list;
      }
      /// <summary>
      /// 根据订单编号查询相应的商品信息
      /// </summary>
      /// <returns></returns>
      public string GetPurchaseMateByPurchase_Id(int Purchase_Id)
      {
          StringBuilder builder = new StringBuilder("select PurchaseMate.PurchaseMate_Id,Product_Name,PurchaseMate_Num,PurchaseMate_Price,PurchaseMate.SumMoney,Employee_Name from PurchaseMate join Purchase on Purchase.Purchase_Id=PurchaseMate.Purchase_Id join Product on Product.Product_Id=PurchaseMate.Product_Id join Employee on Employee.Employee_Id=Purchase.Employee_Id where Purchase.Purchase_Id=@Purchase_Id");
          SqlParameter[] par = { 
                               new SqlParameter("@Purchase_Id",SqlDbType.Int,4)
                               
                               };
          par[0].Value = Purchase_Id;
          DataTable table = helper.ExecDataTable(builder.ToString(),par);
          JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
          List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
          Dictionary<string, object> childRow;
          foreach (DataRow row in table.Rows)
          {
              childRow = new Dictionary<string, object>();
              foreach (DataColumn col in table.Columns)
              {
                  childRow.Add(col.ColumnName, row[col]);
              }
              parentRow.Add(childRow);
          }

          return jsSerializer.Serialize(parentRow);
      }
      /// <summary>
      /// 根据编号获取仓库，供货商，下单人的信息
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public string GetPurchaseByid(int id)
      {
          StringBuilder builder = new StringBuilder("select ROW_NUMBER() over(order by Purchase_Id)nid,Purchase_Id,Purchase_date,Purchase_State,Supplier_Name,WareHose_Name,Employee_Name from Purchase join Supplier on Purchase.Supplier_Id=Supplier.Supplier_Id join WareHose on Purchase.WareHose_Id=WareHose.WareHose_Id join Employee on Employee.Employee_Id=Purchase.Employee_Id where Purchase.Purchase_Id=@Purchase_Id");
          SqlParameter[] par = { 
                               new SqlParameter("@Purchase_Id",SqlDbType.Int,4)
                               
                               };
          par[0].Value = id;
         DataTable table= helper.ExecDataTable(builder.ToString(), par);
          JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
          List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
          Dictionary<string, object> childRow;
          foreach (DataRow row in table.Rows)
          {
              childRow = new Dictionary<string, object>();
              foreach (DataColumn col in table.Columns)
              {
                  childRow.Add(col.ColumnName, row[col]);
              }
              parentRow.Add(childRow);
          }

          return jsSerializer.Serialize(parentRow);
      }
      /// <summary>
      /// 根据编号获取仓库，供货商，下单人的信息
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int Proc_Ruku(int id)
      {
          SqlParameter[] par = { 
                               new SqlParameter("@Purchase_Id",id)
                               };
          return helper.ExecNonquery_Proc("Proc_ruku", par);
      }
    }
}
    